﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections.Specialized;

namespace DataAccess
{
    /// <summary>
    /// Sql操作类型．
    /// </summary>
    public enum SqlOperationType { SimpleSelect, SelectByKey, SelectByParent, Insert, Update, DeleteByParent, Delete, Others, StoredProcedure }
    /// <summary>
    /// 构造Sql语句．
    /// </summary>
    public class SqlGenerator
    {
        private SqlGenerator() { }
        private static SqlGenerator singleton = new SqlGenerator();
        public static SqlGenerator Instance()
        {
            return singleton;
        }

        public SqlStruct GenerateSql(DataSet entity, SqlOperationType sqlOperationType)
        {
            if (entity.Tables.Count < 1)
                throw new Exception("Table 0 does not exist!");
            return GenerateSql(entity.Tables[0], sqlOperationType);
        }
        public SqlStruct GenerateSql(DataSet entity, int table, SqlOperationType sqlOperationType)
        {
            if (entity.Tables[table] == null)
                throw new Exception("Table " + table + " does not exist!");
            return GenerateSql(entity.Tables[table], sqlOperationType);
        }
        public SqlStruct GenerateSql(DataSet entity, string table, SqlOperationType sqlOperationType)
        {
            if (!entity.Tables.Contains(table))
                throw new Exception("Table " + table + " does not exist!");
            return GenerateSql(entity.Tables[table], sqlOperationType);
        }

        public SqlStruct GenerateSql(DataTable table, SqlOperationType sqlOperationType)
        {
            SqlStruct sqlStruct;
            DataSet entity = table.DataSet;
            //			string key = null;

            DataColumn[] columns = table.PrimaryKey;
            string[] keyColumns = new string[columns.Length];
            for (int i = 0; i < columns.Length; i++)
            {
                keyColumns[i] = columns[i].ColumnName;
            }

            switch (sqlOperationType)
            {
                case SqlOperationType.SimpleSelect:
                    sqlStruct = new SqlStruct(GenerateSimpleSelectSql(table), null);
                    break;
                case SqlOperationType.SelectByKey:
                    sqlStruct = GenerateFindByKeySql(table, keyColumns);
                    break;
                case SqlOperationType.Insert:
                    sqlStruct = GenerateInsertSql(table, keyColumns);
                    break;
                case SqlOperationType.Update:
                    sqlStruct = GenerateUpdateSql(table, keyColumns);
                    break;
                case SqlOperationType.Delete:
                    sqlStruct = GenerateDeleteSql(table, keyColumns);
                    break;
                case SqlOperationType.SelectByParent:
                    sqlStruct = GenerateFindByParentSql(table);
                    break;
                case SqlOperationType.DeleteByParent:
                    sqlStruct = GenerateDeleteByParentSql(table);
                    break;
                default:
                    sqlStruct = null;
                    break;
            }
            return sqlStruct;
        }

        private string GenerateSimpleSelectSql(DataTable table)
        {
            string[] columns = new string[table.Columns.Count];
            for (int i = 0; i < columns.Length; i++)
                columns[i] = table.Columns[i].ColumnName;
            return new StringBuilder("SELECT ").Append(string.Join(",", columns)).Append(" FROM ").Append(table.TableName).ToString();
        }

        private SqlStruct GenerateInsertSql(DataTable table, string[] keyColumns)
        {
            string[] columns = new string[table.Columns.Count];
            string[] parameters = new string[table.Columns.Count];
            ParamField[] paramField = new ParamField[table.Columns.Count];
            for (int i = 0; i < columns.Length; i++)
            {
                columns[i] = table.Columns[i].ColumnName;
                if (PersistenceProperty.DatabaseType == DatabaseType.MSSQLServer)
                    parameters[i] = "@" + columns[i];
                else
                    parameters[i] = ":" + columns[i];
                paramField[i] = new ParamField(parameters[i], columns[i]);
            }
            return new SqlStruct(new StringBuilder("INSERT INTO ").Append(table.TableName).Append("(").Append(string.Join(",", columns)).Append(") VALUES(").Append(string.Join(",", parameters)).Append(")").ToString(), paramField);
        }

        private SqlStruct GenerateUpdateSql(DataTable table, string[] keyColumns)
        {
            //首先，得到所有的列
            NameValueCollection allColumns = new NameValueCollection();
            for (int i = 0; i < table.Columns.Count; i++)
            {
                allColumns.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
            }

            //然后，去除主键列
            for (int i = 0; i < keyColumns.Length; i++)
            {
                allColumns.Remove(keyColumns[i]);
            }

            string[] setString = new string[allColumns.Count];
            ParamField[] paramField = new ParamField[allColumns.Count + keyColumns.Length];
            for (int i = 0; i < allColumns.Count; i++)
            {
                if (PersistenceProperty.DatabaseType == DatabaseType.MSSQLServer)
                {
                    setString[i] = new StringBuilder(allColumns[i]).Append("=@").Append(allColumns[i]).ToString();
                    paramField[i] = new ParamField("@" + allColumns[i], allColumns[i]);
                }
                else
                {
                    setString[i] = new StringBuilder(allColumns[i]).Append("=:").Append(allColumns[i]).ToString();
                    paramField[i] = new ParamField(":" + allColumns[i], allColumns[i]);
                }
            }

            string[] whereString = new string[keyColumns.Length];
            for (int i = 0; i < keyColumns.Length; i++)
            {
                if (PersistenceProperty.DatabaseType == DatabaseType.MSSQLServer)
                {
                    whereString[i] = new StringBuilder(keyColumns[i]).Append("=@").Append(keyColumns[i]).ToString();
                    paramField[allColumns.Count + i] = new ParamField("@" + keyColumns[i], keyColumns[i]);
                }
                else
                {
                    whereString[i] = new StringBuilder(keyColumns[i]).Append("=:").Append(keyColumns[i]).ToString();
                    paramField[allColumns.Count + i] = new ParamField(":" + keyColumns[i], keyColumns[i]);
                }
            }

            return new SqlStruct(new StringBuilder("UPDATE ").Append(table.TableName).Append(" SET ").Append(string.Join(",", setString)).Append(" WHERE ").Append(string.Join(" AND ", whereString)).ToString(), paramField);
        }

        private SqlStruct GenerateDeleteSql(DataTable table, string[] keyColumns)
        {
            string[] whereString = new string[keyColumns.Length];
            ParamField[] paramField = new ParamField[keyColumns.Length];
            for (int i = 0; i < keyColumns.Length; i++)
            {
                if (PersistenceProperty.DatabaseType == DatabaseType.MSSQLServer)
                {
                    whereString[i] = new StringBuilder(keyColumns[i]).Append("=@").Append(keyColumns[i]).ToString();
                    paramField[i] = new ParamField("@" + keyColumns[i], keyColumns[i]);
                }
                else
                {
                    whereString[i] = new StringBuilder(keyColumns[i]).Append("=:").Append(keyColumns[i]).ToString();
                    paramField[i] = new ParamField(":" + keyColumns[i], keyColumns[i]);
                }
            }
            return new SqlStruct(new StringBuilder("DELETE FROM ").Append(table.TableName).Append(" WHERE ").Append(string.Join(" AND ", whereString)).ToString(), paramField);
        }
        private SqlStruct GenerateFindByKeySql(DataTable table, string[] keyColumns)
        {
            string[] columns = new string[table.Columns.Count];
            for (int i = 0; i < columns.Length; i++)
            {
                columns[i] = table.Columns[i].ColumnName;
            }

            string[] whereString = new string[keyColumns.Length];
            ParamField[] paramField = new ParamField[keyColumns.Length];
            for (int i = 0; i < keyColumns.Length; i++)
            {
                if (PersistenceProperty.DatabaseType == DatabaseType.MSSQLServer)
                {
                    whereString[i] = new StringBuilder(keyColumns[i]).Append("=@").Append(keyColumns[i]).ToString();
                    paramField[i] = new ParamField("@" + keyColumns[i], keyColumns[i]);
                }
                else
                {
                    whereString[i] = new StringBuilder(keyColumns[i]).Append("=:").Append(keyColumns[i]).ToString();
                    paramField[i] = new ParamField(":" + keyColumns[i], keyColumns[i]);
                }
            }
            return new SqlStruct(new StringBuilder("SELECT ").Append(string.Join(",", columns))
                .Append(" FROM ").Append(table.TableName).Append(" WHERE ").Append(string.Join(" AND ", whereString)).ToString(), paramField);

        }

        private SqlStruct GenerateFindByParentSql(DataTable table)
        {
            string[] columns = new string[table.Columns.Count];
            for (int i = 0; i < columns.Length; i++)
            {
                columns[i] = table.Columns[i].ColumnName;
            }

            string parentTable = table.DataSet.Tables[0].TableName;
            string foreignKey = "";
            DataRelationCollection relation = table.DataSet.Relations;
            for (int i = 0; i < relation.Count; i++)
            {
                if (relation[i].ParentTable.TableName.Equals(parentTable)
                    && relation[i].ChildTable.TableName.Equals(table.TableName))
                {
                    foreignKey = relation[i].ChildColumns[0].ColumnName;
                }
            }
            string whereString;
            ParamField[] paramField;
            if (PersistenceProperty.DatabaseType == DatabaseType.MSSQLServer)
            {
                whereString = foreignKey + "=@" + foreignKey;
                paramField = new ParamField[1];
                paramField[0] = new ParamField("@" + foreignKey, foreignKey);
            }
            else
            {
                whereString = foreignKey + "=:" + foreignKey;
                paramField = new ParamField[1];
                paramField[0] = new ParamField(":" + foreignKey, foreignKey);
            }
            return new SqlStruct(new StringBuilder("SELECT ").Append(string.Join(",", columns)).Append(" FROM ").Append(table.TableName).Append(" WHERE ").Append(whereString).ToString(), paramField);

        }

        private SqlStruct GenerateDeleteByParentSql(DataTable table)
        {
            string parentTable = table.DataSet.Tables[0].TableName;
            string foreignKey = "";
            DataRelationCollection relation = table.DataSet.Relations;
            for (int i = 0; i < relation.Count; i++)
            {
                if (relation[i].ParentTable.TableName.Equals(parentTable)
                    && relation[i].ChildTable.TableName.Equals(table.TableName))
                {
                    foreignKey = relation[i].ChildColumns[0].ColumnName;
                }
            }

            string whereString;
            ParamField[] paramField;
            if (PersistenceProperty.DatabaseType == DatabaseType.MSSQLServer)
            {
                whereString = foreignKey + "=@" + foreignKey;
                paramField = new ParamField[1];
                paramField[0] = new ParamField("@" + foreignKey, foreignKey);
            }
            else
            {
                whereString = foreignKey + "=:" + foreignKey;
                paramField = new ParamField[1];
                paramField[0] = new ParamField(":" + foreignKey, foreignKey);
            }

            return new SqlStruct(new StringBuilder("DELETE FROM ").Append(table.TableName).Append(" WHERE ").Append(whereString).ToString(), paramField);

        }

        private string GenerateKey(string entityName, string tableName, SqlOperationType sqlOperationType)
        {
            return new StringBuilder(entityName).Append("__").Append(tableName).Append("__").Append(sqlOperationType.ToString()).ToString();
        }
    }
}
